﻿using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace PostgreSQLExportInsert
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }


        ///
        /// 根据填写的信息拼数据库连接串
        ///
        /// 数据库连接串
        private string getConnectStr()
        {
            StringBuilder sb = new StringBuilder();
            string str = string.Empty;
            sb.Append("Server=");
            sb.Append(txtServer.Text);
            sb.Append(";Port=");
            sb.Append(txtPort.Text);
            sb.Append(";User Id=");
            sb.Append(txtUID.Text);
            sb.Append(";Password=");
            sb.Append(txtPassword.Text);
            sb.Append(";Database=");
            sb.Append(txtDB.Text);
            sb.Append(";");
            str = sb.ToString();
            return str;
        }


        ///
        /// 测试连接PostGreSQL数据库
        ///
        /// Success/Failure
        private string TestConnection()
        {
            string str = getConnectStr();
            string strMessage = string.Empty;
            try

            {
                NpgsqlConnection conn = new NpgsqlConnection(str);
                conn.Open();
                strMessage = "Success";
                conn.Close();
            }
            catch
            {
                strMessage = "Failure";
            }
            return strMessage;
        }


        ///
        /// 测试获取数据（使用要加try、catch）
        ///
        private void TestGetData()
        {
            IDbConnection dbcon;
            dbcon = new NpgsqlConnection(getConnectStr());
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            dbcmd.CommandText = " SELECT * FROM "+ txtTable.Text;
            IDataReader dr = dbcmd.ExecuteReader();
            string strResult = string.Empty;

            while (dr.Read())
            {
                string appstr = "insert into "+ txtTable.Text;
                string columns = "";
                string values = "";
                for (int x=0;x<dr.FieldCount;x++)
                {
                    if(x==0)
                    {
                        columns = dr.GetName(x);

                        if(dr.IsDBNull(x))
                        {
                            values = "NULL";
                        }
                        else
                        {
                            values = "'"+ dr[x]+ "'";
                        }
                    }
                    else
                    {
                        columns += ","+dr.GetName(x);

                        if (dr.IsDBNull(x))
                        {
                            values += "," + "NULL";
                        }
                        else
                        {
                            values += "," + "'" + dr[x] + "'";
                        }
                    }
                }

                appstr = appstr + "(" +columns+ ") values("+values+");\r\n";

                strResult += appstr;

            }
            dr.Close();
            dr = null;
            txtContent.Text = strResult;
        }

        private void btnCreate_Click(object sender, RoutedEventArgs e)
        {
            TestGetData();
        }
    }
}
